
if exists(select name from sysobjects where name = 'sp_DiemGetDiemThanhPhan')
	drop proc sp_DiemGetDiemThanhPhan
go
/*
	Created by: Phuoc NH
	Created on: 04/012/2013 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_DiemGetDiemThanhPhan.sql
*/

create proc sp_DiemGetDiemThanhPhan
@MaSinhVien varchar(50),
@Mamon varchar(50)
as
begin
	select b.*,c.TenMon, a.MaMon from BangDiem a inner join Diem b on a.LopMonID=b.LopMonID and a.MaSinhVien=b.MaSinhVien inner join Mon c on c.MaMon=a.MaMon where a.MaSinhVien=@MaSinhVien and a.MaMon=@Mamon order by CASE LoaiDiem
        WHEN 'DT' THEN 1
        WHEN 'DTL' THEN 2
        WHEN 'TB' THEN 3
        ELSE 0
    END
	
	
--select * from (select loaidiem, CONVERT(float, b.Diem) as diem from BangDiem a inner join Diem b on a.LopMonID=b.LopMonID and a.MaSinhVien=b.MaSinhVien where a.MaSinhVien=@MaSinhVien and a.MaMon=@Mamon) as tb
--pivot(
--	sum(diem)
--	for loaidiem in(bt,cc,dt,dtl,kt,tb)
--	) as ptb
	
end

